package com.lockmem.DB;

import java.util.ArrayList;
import java.util.List;

import com.lockmem.constants.Constants;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class DBHelper extends SQLiteOpenHelper {
	private Context ser = null;
	private String tag = "DBHelper";
	private SQLiteDatabase mydb = null;

	public DBHelper(Context context) {
		// TODO Auto-generated constructor stub
		super(context, "mydb", null, Constants.Current_DB_Version);
		ser = context;
	}

	public SQLiteDatabase getDB() {
		if (mydb == null || !mydb.isOpen())
			mydb = this.getWritableDatabase();
		return mydb;
	}

	public int getRemTimes() {
		// TODO Auto-generated method stub
		SQLiteDatabase db = getDB();
		String sql = String.format("select %s from %s",
				RowEnglish.RowName_Remember_Times, RowEnglish.tablename);
		Cursor cs = db.rawQuery(sql, null);
		cs.moveToFirst();
		int i = cs.getInt(0);
		// cs.close();
		// db.close();
		return i;
	}

	public List<Row> getRows(String tablename, int count, String now,
			int order, boolean needWrongWord) {
		List<Row> rowl = new ArrayList<Row>();
		Cursor cs = null;
		String orderS = null;
		Log.v(tag, "order" + String.valueOf(order));
		if (order == 0)
			orderS = "english ";
		else if (order == 1)
			orderS = "english desc";
		String sql = null;
		if (tablename == RowEnglish.tablename) {
			if (order < 2) {
				sql = String
						.format("select %s,%s,%s,%s,%s,%s from %s where %s in(select %s from %s where %s > -1 and %s < %s order by %s desc, %s limit %d) order by %s %s",
								RowEnglish.RowName_english, // 0
								RowEnglish.RowName_chinese, // 1
								RowEnglish.RowName_Last_Visit_Time, // 2
								RowEnglish.RowName_Next_Visit_Time, // 3
								RowEnglish.RowName_Test_Wrong, // 4
								RowEnglish.RowName_Remember_Times, // 5
								RowEnglish.tablename,
								RowEnglish.RowName_english,
								RowEnglish.RowName_english,
								RowEnglish.tablename,
								RowEnglish.RowName_Remember_Times,
								RowEnglish.RowName_Next_Visit_Time, now,
								RowEnglish.RowName_Remember_Times,
								RowEnglish.RowName_Next_Visit_Time, count,
								needWrongWord ? "test_wrong desc," : "", orderS);
			} else {
				sql = String
						.format("select %s,%s,%s,%s,%s,%s from %s where %s > -1 and %s < %s order by %s %s desc, %s, random() limit %d ",
								RowEnglish.RowName_english, // 0
								RowEnglish.RowName_chinese, // 1
								RowEnglish.RowName_Last_Visit_Time, // 2
								RowEnglish.RowName_Next_Visit_Time, // 3
								RowEnglish.RowName_Test_Wrong, // 4
								RowEnglish.RowName_Remember_Times, // 5
								RowEnglish.tablename,
								RowEnglish.RowName_Remember_Times,
								RowEnglish.RowName_Next_Visit_Time, now,
								needWrongWord ? "test_wrong desc," : "",
								RowEnglish.RowName_Remember_Times,
								RowEnglish.RowName_Next_Visit_Time, count);
			}

			cs = getDB().rawQuery(sql, null);
			for (cs.moveToFirst(); !cs.isAfterLast(); cs.moveToNext()) {
				Row row = new RowEnglish();
				((RowEnglish) row).set(cs.getString(1), cs.getString(0),
						cs.getInt(5), cs.getString(2), cs.getString(3),
						cs.getInt(4));
				rowl.add(row);
			}
		}
		// cs.close();
		// db.close();
		return rowl;
	}

	public RowUserData getUserData(String current_tablename) {
		// TODO Auto-generated method stub
		String sql = String.format(
				"select %s,%s,%s,%s,%s,%s,%s from %s where %s = '%s'",
				RowUserData.key_Last_unlock_day, // 0
				RowUserData.key_Learned_items, // 1
				RowUserData.key_Name, // 2
				RowUserData.key_Tablename, // 3
				RowUserData.key_Today_Unlock_times, // 4
				RowUserData.key_Unlearned_items, // 5
				RowUserData.key_Unlock_times, RowUserData.tablename,
				RowUserData.key_Tablename, current_tablename);
		Cursor cs = getDB().rawQuery(sql, null);
		if (!cs.moveToFirst()) {
			try {
				throw new Exception("NO USERDATA EXCEPTION !");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		RowUserData rud = new RowUserData(cs.getString(2), cs.getString(3),
				cs.getInt(6), cs.getInt(4), cs.getInt(0), cs.getInt(1),
				cs.getInt(5));
		// cs.close();
		// db.close();
		return rud;
	}

	/**
	 * @param string
	 * @param string2
	 */
	public void unlock(String tablename, String today) {
		if (tablename.equals(RowEnglish.tablename)) {
			if (today.equals(getLastunLockday(tablename))) {
				String sql = String.format(
						"update %s set %s=%s+1,%s=%s+1 where %s='%s' ",
						RowUserData.tablename,
						RowUserData.key_Today_Unlock_times,
						RowUserData.key_Today_Unlock_times,
						RowUserData.key_Unlock_times,
						RowUserData.key_Unlock_times,
						RowUserData.key_Tablename, tablename);
				getDB().execSQL(sql);
			} else {
				String sql = String.format(
						"update %s set %s=1,%s=%s+1,%s=%s where %s='%s'",
						RowUserData.tablename,
						RowUserData.key_Today_Unlock_times,
						RowUserData.key_Unlock_times,
						RowUserData.key_Unlock_times,
						RowUserData.key_Last_unlock_day, today,
						RowUserData.key_Tablename, tablename);
				getDB().execSQL(sql);
			}
			// db.close();
		}
	}

	public void updateRow(String tablename, String english,
			int remembered_Times, String next_time, String last_time) {
		// TODO Auto-generated method stub
		if (tablename.equals(RowEnglish.tablename)) {
			String sql = String.format(
					"update %s set %s=%d,%s='%s',%s='%s' where %s='%s'",
					tablename, RowEnglish.RowName_Remember_Times,
					remembered_Times, RowEnglish.RowName_Next_Visit_Time,
					next_time, RowEnglish.RowName_Last_Visit_Time, last_time,
					RowEnglish.RowName_english, english);
			getDB().execSQL(sql);
			// db.close();
		}
	}

	public String getLastunLockday(String tablename) {
		// TODO Auto-generated method stub
		String sql = String.format("select %s from %s where %s = '%s'",
				RowUserData.key_Last_unlock_day, RowUserData.tablename,
				RowUserData.key_Tablename, tablename);
		Cursor cs = getDB().rawQuery(sql, null);
		cs.moveToFirst();
		// Toast.makeText(ser, cs.getString(0), Toast.LENGTH_SHORT).show();
		String s = cs.getString(0);
		// cs.close();
		// db.close();
		return s;
	}

	/**
	 * @param english
	 */
	public void gotIt(String tablename, String english) {
		// TODO Auto-generated method stub
		if (tablename.equals(RowEnglish.tablename)) {
			String sql = String.format("update %s set %s=-1 where %s='%s'",
					tablename, RowEnglish.RowName_Remember_Times,
					RowEnglish.RowName_english, english);
			getDB().execSQL(sql);

			sql = String.format("update %s set %s=%s+1,%s=%s-1 where %s='%s' ",
					RowUserData.tablename, RowUserData.key_Learned_items,
					RowUserData.key_Learned_items,
					RowUserData.key_Unlearned_items,
					RowUserData.key_Unlearned_items, RowUserData.key_Tablename,
					tablename);
			getDB().execSQL(sql);
			// db.close();
		}
	}

	/**
	 * @param tablename
	 * @param english
	 */
	public void setRowTestWrong(String tablename, String english) {
		// TODO Auto-generated method stub
		if (tablename.equals(RowEnglish.tablename)) {
			String sql = String.format("update %s set %s=1 where %s='%s' ",
					tablename, RowEnglish.RowName_Test_Wrong,
					RowEnglish.RowName_english, english);
			getDB().execSQL(sql);
			// db.close();
		}
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String dbStr = FileCtrl.getDBString(ser);
		// db.execSQL("create TABLE if not exists t_words ( not_sure TEXT, test_wrong INTEGER, remembered_times INTEGER, last_visit_time TEXT, next_visit_time TEXT, english text PRIMARY KEY, chinese text );");
		String[] dbss = dbStr.split("\n");
		db.execSQL("create table if not exists t_words ( 'not_sure' TEXT, 'test_wrong' INTEGER, 'remembered_times' INTEGER, 'last_visit_time' TEXT, 'next_visit_time' TEXT, 'english' text PRIMARY KEY, 'chinese' text );");
		db.execSQL("create table if not exists gongwuyuan ( 'not_sure' TEXT, 'test_wrong' INTEGER, 'remembered_times' INTEGER, 'last_visit_time' TEXT, 'next_visit_time' TEXT, 'english' text PRIMARY KEY, 'chinese' text );");
		db.execSQL("CREATE TABLE if not exists userData (Name TEXT, Tablename TEXT, Unlock_times INTEGER, Today_Unlock_times INTEGER, Last_unlock_day INTEGER, Learned_items INTEGER, Unlearned_items IETEGER,IDnum integer);");
		for (int i = 0; i < dbss.length; i++) {
			Log.i("i",dbss[i]);
			db.execSQL(dbss[i]);
		}

		Log.v("DBHelper", "create DB");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("drop table if exists t_words");
		db.execSQL("drop table if exists userData");
		db.execSQL("drop table if exists gongwuyuan");
		onCreate(db);
	}

	public ResNameNumPair[] getAllUserDataNameNumPair() {
		// TODO Auto-generated method stub
		Cursor mCursor = getDB().rawQuery(
				"select Name,idnum from userData order by idnum", null);
		List<ResNameNumPair> pairs = new ArrayList<ResNameNumPair>();
		for (mCursor.moveToFirst(); !mCursor.isAfterLast(); mCursor
				.moveToNext()) {
			pairs.add(new ResNameNumPair(mCursor.getInt(1),mCursor.getString(0)));
		}
		return pairs.toArray(new ResNameNumPair[pairs.size()]);
	}
}
